in when printing. The date field types include both the
format to print and the format of the date in the data file.
A Alphanumeric. This is used to define any Alphanumeric field, using upper-case characters only.
L Lower Case. This is the same as "Alpha" except that it will allow a lower case character as well as an upper case character.
U Used to shift all alphabetic characters to upper case before the field is printed.
P Telephone number. Used to output in phone number format
PE Telephone number and extension.
G GPS account number
N Number with commas (e.g. 2,000).
If the number is zero, then nothing is printed.
This is equivalent to Blank when Zero.
N- Signed number with commas (e.g. -2,000).
If the number if zero, then nothing is printed.
# Number with commas.
If the number is zero, then a 0 is printed.
#- Signed number with commas.
If the number is zero, then a 0 is printed.
* Number without commas (e.g. 2000).
If the number is zero, then nothing is printed.
*- Signed number without commas (e.g. -2000).
If the number is zero, then nothing is printed.
@ Number without commas (e.g. 2000).
If the number is zero, then a 0 is printed.
@- Signed number without commas (e.g. -2000).
If the number is zero, then a 0 is printed.
1- Signed number with one decimal place (e.g. -100.0)
2 Number with two decimal places (e.g. 100.00)
2- Signed number with two decimal places (e.g. -100.00)
3 Number with three decimal places (e.g. 100.000)
3- Signed number with three decimal places (e.g. -100.000)
4 Number with four decimal places (e.g. 100.0000)
4- Signed number with four decimal places (e.g. -100.0000)
5 Number with five decimal places (e.g. 100.00000)
5- Signed number with five decimal places (e.g. -100.00000)
6 Number with six decimal places (e.g. 100.000000)
6- Signed number with six decimal places (e.g. -100.000000)
7 Number with seven decimal places (e.g. 100.0000000)
7- Signed number with seven decimal places (e.g. -100.0000000)
8 Number with eight decimal places (e.g. 100.00000000)
8- Signed number with eight decimal places (e.g. -100.00000000)
9 Number with nine decimal places (e.g. 100.000000000)
9- Signed number with nine decimal places (e.g. -100.000000000)
NOTE: The preceding numeric field types from 1-9 always
include commas and will print 0's after the decimal point if 0,
unless you use the .format field command.
D Date. The first letter of a date field type is used as to indicate the output format. D is the default date format supplied with the program (dd/mm/yy).
The second letter of the date format indicates the
input (storage) format.
Following are field types for the date formats:
Da mm/dd/yy Db ddlllyy Dc yynnn
Dd yy/mm/dd De dd/mm/yy Df ddlllccyy
Dg dd/mm/ccyy Dh ccyymmdd Di yymmdd
Dj ccyynnn Dk mm/dd/ccyy Dl dd lll yy
Dm dd lll ccyy Dn ll dd, yy Do lll dd, ccyy
Dp dd.mm.yy Dq dd.mm.ccyy Dr mm.dd.yy
Ds mm.dd.ccyy Dt jjjjjj* Du ddmmyy
Dv ddoyy* Dw bitmap 1* Dx Send Sols jjjjjj
Dz Elapsed (ZDAYS) jjjjjj DM mmddyy
mm = month
dd = day
yy = year
lll = month literal
nnn = year's julian date number
jjjjjj = julian date with one at January 1, 0000*
ccyy = century, year
AH Hex Format.
This will allow you to output an alphanumeric field in hex format.
T Time stamp (HH:MM:SS)
Q Question.
This is the same as "Alpha" except that the program expects the field to contain only a Y or N.
The field type entered will be one character, except in the case of dates, hex and negative numbers. The second character can be a minus sign (-) in the case of negative numbers. Comp-3's always have a "-". Any other usage of negative numbers depends upon your application.
* Section *
17.5.1 Julian Date Notes
The date type Dt is described above as the Julian date starting at January 1, 0000. When using this date type for elapsed 'Julian' days since a given date, such as January 1, 1900, this does work for display, since only the century is different, but does not work for selection criteria or in calculations.
The date type Dz can be used. This is defined as the elapsed days since a given date. The default starting date is January 1, 1900, but may be changed by the user. When a Dz field is first encountered, Atlantis™ Data Publisher looks for a ZDAYS.DAT file. The ZDAYS.DAT file contains the Julian date for the beginning of the elapsed days count. For example, January 1, 1900 is Julian date 693975 and January 1, 1980 is Julian date 723195. This starting Julian date for elapsed days is put in the ZDAYS.DAT file, using any ASCII text editor (do not put any commas in the number). An example of ZDAYS.DAT file is included in the pgms folder.
* Section *
17.5.2 Single Digit Month Date Format (ddoyy-)
The ddoyy date format allows for the single-digit month. The date is stored as 5-digit signed number. This number is interpreted as follows:
- The leftmost 2 digits equal the day of the month.
- The rightmost 2 digits equal the year, assuming that 00 is 1900.
- The middle digit is always a number from 1 to 6. When the number is positive the month is equal to the middle digit.
When the number is negative than the month is equal to the middle digit + 6.
Examples:
13588 is May 13, 1988
The day is 13
The year is 1900 + 88 = 1988
The month is 5, since 13588 is positive.
-13588 is November 13, 1988
The day is 13The year is 1900 + 88 = 1988
The month is 5 + 6 = 11, since -13588 is negative.
* Section *
17.5.3 Bit Mapped Format
The date format of w allows for a date that is bitmapped into a two-byte unsigned number.
The five least significant bits (B0 through B4) equal the day of the month. The next four bits (B5 through B8) are the month. The 7 most significant bits (B9 through B15) are the year.
Example:
Consider the decimal number 45,298:
In hexadecimal it is B0F2.
This translates into binary form as:
B 0 F 2
1011 0000 1111 0010
The day of the month is equal to the 5 least significant bits or the first 5 from the right. This gives the number:
10010 in binary =
18 in decimal,
therefore the day of the month is 18.
The next 4 bits from the right represent the month. This gives us the number:
0111 in binary =
7 in decimal,
therefore the month is 7 or July.
The 7 most significant bits or the first 7 from the left represent the year assuming that 0 is 1900. This yields the number:
1011000 in binary =
88 in decimal,
therefore the year is 1900 + 88 = 1988.
This gives us the date July 18, 1988.
* Section *
17.6 Max Number of Char
Enter the maximum size of the field. This is the maximum number of characters that are allowed in the field.
When defining numeric fields, allow for integers, decimal points and commas (if not implied or hidden). Do not allow for commas or for numbers after the decimal point. Also, do not allow for commas which are to be printed (as specified at Field Type), but which are not part of the field value itself.
Note: In the case of C-Basic variable length fields, enter the maximum number of characters possible for the field. For variations on this, refer to Defining or Re-Defining Portions of a Field and Splitting Fields, both covered later in this chapter after Field Start Location.
* Section *
17.7 Format of Data in File
Select from the list one of the following codes to indicate how the field's data is stored. This code tells the program what kind of translation to do on the data when it reads it (if any). Leave blank if the field has no special storage format. If you are unsure, check your application's documentation.
(Blank) Normal Display Formats. (No change of format required)
This can be used for regular alpha and numeric fields.
0 COBOL COMP (BCD: 0 to 9 and OD means negative)
1 COBOL COMP-1 (Same as Short Integer)
3 COBOL COMP-3 (signed compressed BCD number)
4 4-byte integer (Tandy Basic)
6 COBOL COMP-6 (unsigned compressed BCD number)
8 8-byte integer (Tandy Basic)
^ DataFlex numerics and dates
! Basic Single Precision (MicroSoft and IBM Basic)
(4 byte internal size)
# Basic Double Precision (MicroSoft and IBM Basic)
(8 byte internal size)
% 2 byte integer (same as Short Integer)
& 2 byte integer (same as % format, except
reverses order of numbers)
> MicroSoft Fortran Double Decimal Number (8 bytes)
< MicroSoft Fortran Single Decimal Number (4 bytes)
A ASCII character (0 to 255 value)
B Signed byte (-128 to 127 value)
C Combined sign on last display-format digit
(Micro Focus default)
D Floating point number (double precision)
E COBOL COMP-4 Two's complement binary number
F Floating point number (single precision)
G BCD packed numbers
H Informix Small Integer
I Short Integer (-32768 to 32767 value)
L Long Integer
M Informix Double "Money"
N Null terminated strings - fixed length fields, strings
less than the maximum field size are terminated with a null
P Lattice C-Food packed number
R Turbo Pascal 6-byte real numbers
S Separate Sign (same as blank)
T Time stamp (MSDOS FCB form)
U Unsigned short Integer (0 to 65535 values)
V Micro Focus COBOL Comp format
W Realia Comp-4 S9(9)
X Realia Comp-4 S9(18)
Y Date stamp (MSDOS FCB form)
Z Realia Comp-5 S9(18)
* Section *
17.7.1 Notes on COBOL Formats
COMP. (Computational) The value is stored as a binary number, rather than as an ASCII digit, one byte per digit. If a sign is specified and if there is a negative code at the trailing byte, computational items are treated as negative. A hexadecimal "0D" is treated as negative. All other values are treated as positive.
COMP-1. This is a signed, 2-byte binary number. Regardless of how many decimal places are in this field, it remains 2 bytes unless this field is on the receiving end of an operand. The value of a Comp-1 item ranges between -32768 to +32767.
COMP-3. This is sometimes referred to as a packed decimal or Binary Coded Decimal (BCD). Data items that are Comp-3 are allocated memory at one-half character position for each numeral, plus an extra half position for the sign whether specified or not. The half position for the sign is "0D" for negative. Anything else is treated as positive. If the number of decimal places is even, it is treated as if there is another decimal place at the beginning of the field.
Comp-3's are always signed and should have a "-" as the second character when defining the Field Type above.
To calculate the physical size in a record of a Comp-3 field, add up all the digits specified, add one for a sign (whether one is specified or not), divide by two and round up.
Following is an example of calculating start location with COBOL source code:
Type Size Decimal Location Picture
Alpha 10 1 PIC X(10)
Comp-3 05 3 11 PIC 9(5)V999
Alpha 05 16 PIC X(5)
20 = Record Size
The above was calculated as follows:
1. To get the next field starting location, add the previous field's size and starting location, 10 (field size) plus 1 (starting location) = 11.
2. The Comp-3 size was computed as follows: Add 5 plus 4 (include the sign as it does take up space) and you have 9, divide 9 by 2 and you have 4.5, round up 4.5 and you have 5. You round up to fit the "uneven byte".
3. Now add 5 plus 11. Your starting location for the next field is 16.
Example: In the Comp-3 format +1234 (in hex) would look like this:
| 0 1 | 2 3 | 4 C |
byte 1 2 3
COMP-6. This is the same as Comp-3 except that there is no sign allocation, even if an "S" is specified. If the number of decimal places is odd, it is treated as though there was an extra decimal place at the beginning of the field. Thus, an S999 is the same size as 9999 or S9999. This is also known as a packed field.
S. This is used when the byte is stored in "display" format, such as ASCII characters, rather than binary values, and the sign is a separate character. This is also used when the number is stored in a display format.
C. This is used for the trailing sign, combined with the last decimal number. This is the usual default for MF Cobol.
Example: The chart on the next page shows what the values of the signed numbers are. For example, the value of 012r would be "-122".
Associated Sign Positive Character
Value Is
Digit Positive (+) Negative (-)
_________________________________________
0 ( 0 ) p
1 A 1 J q
2 B 2 K r
3 C 3 L s
4 D 4 M t
5 E 5 N u
6 F 6 O v
7 G 7 P w
8 H 8 Q x
9 I 9 R y
* Section *
17.7.2 Basic Formats
! This is for Micro Soft Basic and IBM Basic, to signify the single precision number whose size is four bytes floating decimal point format.
# This is for MS Basic and IBM Basic, to signify the double precision number whose size is 8 bytes floating decimal point format.
% The 2-byte integer. The bytes are internally stored in reverse order on the IBM.
Example:
Byte Byte
01 02 = 201
2-Byte word
* Section *
17.7.3 Dataflex Formats
^ DataFlex numeric and date formats. The Max Number of Char above is the DataFlex field Len times 2, minus the number of decimal points.
Define DataFlex Julian dates as follows:
Max Number of Char 6
Field Type *
Format of Data in File ^
When using the date field for a report, select the date field, and then the option for converting the date. If your Julian date is based on January 1, 0, then use that selection. Fore more details on Julian Dates and mathematical calculations, refer to Chapter 14.
* Section *
17.8 Field Number in Record
Enter either a Field Number in Record or a Field Start Location (next entry), depending on your file type. In some cases, you can enter both. See Defining or Re-Defining Portions of a Field for details.
If your field locations are relative numbers (e.g. 1, 2, 3) rather than byte locations, then enter the relative field number here. This is used for the file types of C-Basic and Open Systems SMC Basic. It is also used for some spreadsheets, such as Lotus 123, where each column is assigned a field number, from left to right.
* Section *
17.9 Field Start Location
Enter the byte location of the first character of the field (unless you entered a field number). Record byte locations start at one.
Example: 1 2 3 4 5 6 7 8 9 10 11 12
J O N E S B U D
In the above example, the first field (last name) takes up eight bytes. The next field (first name) takes up four bytes. The start location is 1 for the first field and 9 for the second field. Some computer systems have hidden fields or hidden bytes. For instance, MicroSoft Cobol Indexed Files have three hidden bytes at the beginning of each record. In that case, the first field starts at byte 4.
The size calculation for some fields is not always straightforward. For example, the numeric COBOL Comp formats are stored in compressed form. (See Format of Data in File.)
* Section *
17.10 Defining or Re-Defining Portions of a Field
It is possible to re-define a portion of a field which has already been defined. This is done by entering both a Field Start Location and a Field Number in Record. Depending on your file type, there are two methods of doing this, as follows.
* Section *
17.10.1 Splitting Fields in C-Basic and Open Systems Files
For these file types, the fields are defined using a relative field number (Field Number in Record), rather than a fixed byte location (Field Start Location). These fields are separated by a delimiter, such as a comma. The field lengths may be variable, but are separated by a comma or other delimiter so that the program knows when the next field starts. Enter the decimal ASCII value of the delimiter under the Field Separator Value on the previous screen, if the separator is not a comma.
To define a portion of one of these fields, you would still enter the relative field number at Field Number in Record. In addition, on the same screen enter the byte position in that field that marks the start of new field definition.
Below is an example:
Field No in Record Field 1 = Name
1 2 3 Field 2 = Age
Field 3 = Birthdate
Record #1 John Jones,32,120154
Record #2 Sally Swanson,32,121254
You want to define the last two digits of Field #3 to get just the year, not a full date. Define another field as:
Field Number in Record 3 (Is a portion of Field #3)Field Start Location 5 (Starts at Location 5 of Field #3)
Maximum Number of Char 2 (You want two characters)
* Section *
17.10.2 Splitting Fields, All Other File Types
Other file types have byte numbers indicating a fixed starting location of a field. Each new field will always start at that byte number. These fields are defined using the Start Location of Field, rather than Field Number in Record.
If your field contains only one type of value, and has no comma delimiters within the fixed length field, then to specify a portion of that field, determine the starting byte position that you need. Enter that byte position as the Field Start Location, and always 0 as the field number in record.
Following is an example of a date field being split, to separate just the year in the last two digits.
Start Location = 20
Max Number of Char = 6
X X X X X X
Byte # 20 25
To define a new field which contains only the last two digits of this field, enter the following:
Start Location = 24
Max Number of Char = 2
Field No in Record = 0
* Section *
17.11 Fixed Length Field Containing Comma-delimiters
As another type of field format, you may have a large fixed-length field which contains several sections, and these sections may be separated by comma delimiters. The full field itself would still be fixed length, but the sections within the field would be variable length and separated by a comma (or other delimiter). If the full fixed length is not used, space is left before the start of the next field (which also starts at a fixed position). In this instance, you would also indicate both the field number in record and the field starting location.
As an example, you could have a field which contains the city, state and zip code for a person. These are separated by commas. In order to define just the city or the state or the zip, you can not indicate a field starting location, as these are variable length entries (within a fixed length field).
You must still specify the Field Start Location, as the start of the full field. Then you must also enter, on the same screen, a field number in record. This field number indicates which field to look for in that large field.
For our example, the full field may be 20 bytes and may start at byte position #25 in the record. If you want to define just the zip code, enter the starting location for the 20-byte field (25). Then enter the Field Number in Record as 3, as it is the third field within that field.
Start
location = 25
X X X X X X X X X X X X X X X X X X X X
<------ Maximum Field Size = 20 ------>
The field could contain three sections, e.g.
City, State, Zip
X X X X X , X X X X X X X , X X X X X
X X X X X X , X X X X X X X , X X X X X
X X X , X X X X X X , X X X X X
The start location is byte #25. The field has three sections within it, which are separated by commas.
These sections could be defined as follows:
City Start Location 25 (Start location of full field)
Field No in Record 1 (Is first section of field)
Max Number of Char 20 (Same as Max for full field)
State Start Location 25 (Start location of full field)
Field No in Record 2 (Is second section of field)
Max Number of Char 20 (Same as Max for full field)
Zip Start Location 25 (Start location of full field)
Field No in Record 3 (Is third section of field)
Max Number of Char 20 (Same as Max for full field)
* Section *
17.12 Fields with a Leading Length Byte
Your file may be set up in such a way that you have a block of fields, in which the block (of fields) has a fixed length, but each of the fields in the block has a variable length, and has a 'leading length byte' which gives the length of that field. Following is an example:
Field 1 Field 2 Field 3 Field 4
Leading Length Byte --> 4 J O H N 5 S M I T H 5 H O M E R 2 A K
In the preceding example, there is one byte before each field value which indicates the length of that field for that particular record. These numbers will vary from record to record, depending on the values within each field.
The Field Start Location for each of these fields will be the byte position of the first field in the block. This is a fixed starting location in the record.
Each of these fields will then also have a relative Field Number in Record, starting with 1 for the first field in the block, 2 for the second field in the block, and so forth. The Max Number of Char for each of these fields is used in the normal fashion and is set to the maximum number of characters that each field can be.
The above example may be defined as follows:
Field Separator Value (first screen) 999
First Name Start Location 1 (Start location of block)
Field No in Record 1 (Is first field of block)
Max Number of Char 10 (First name can be up to 10 char)
Last Name Start Location 1 (Start location of block)
Field No in Record 2 (Is second field of block)
Max Number of Char 15 (Last name can be up to 15 char)
City Start Location 1 (Start location of block)
Field No in Record 3 (Is third field of block
Max Number of Char 15 (City can be up to 15 char)
State Start Location 1 (Start location of full field)
Field No in Record 4 (Is third section of field)
Max Number of Char 2 (State can be only 2 char)
* Section *
17.13 Saving the Field Definition
Click OK to save the field definition. You can now define another field(s) if needed, or you may start designing reports.
* Section *
17.14 Changing Field Definitions
Select Define Files from the Dictionary menu. Select the file name from the list, and click OK to file that definition. The field definition screen will appear. Select the field from the list and make changes. Click OK to save the changes.
* Section *
17.15 Deleting Field Definitions
Select Define Files from the Dictionary menu. Select the file name and then the field name and click on Delete.
* Chapter *
* Section *
18.1 File Relationships
A file relationship must be defined in order to combine data from two or more files into a single report. The second file is included by selecting Special Commands and Choose Field from Another File while in the design. If there are no files listed in the box, then define the relationship.
To start, select Relationship Definitions from the Definitions Menu. This chapter covers the types of file relationships available and how to define each.
This information in this chapter includes:
- what a key field is
- the types of file look-ups
- how to enter the file relationship
- how to use file relationships in your report design
- the mathematical results from some look-ups
There are many things which can be known about file look-ups. This chapter starts with a general overview of how to enter a File Relationship, and then detailed information on each type of look-up is given. See your dealer for an overview of your application's data files, if available. There may be a Data Dictionary (inculding File Relationships) already in existence.
* Section *
18.1.1 Name of First File
The first file is the file which will be doing the looking up. If running a report, it is the file first selected when creating the design. There must be a field in this first file which matches a key field in the file being looked up. (See the section on Key Fields, later in this chapter.) Enter the name of the file as it was defined for use with the Report Writer, not necessarily the name of file on disk. You do not need to enter a folder or directory name.
Example: custdat
* Section *
18.1.2 Name of Lookup File
Enter the name of the file from which you are going to draw the additional data.
For example, you may have a report which starts with the customer sales file, and you need to look up the address in the mailing list file. The first file would be the sales file, and the lookup file would be the mailing list file. A field in the customer file (eg. customer name) would need to match a field in the mailing list file in order for the lookup occur.
Example: maillist
* Section *
18.1.3 Lookup Specification
Here enter the field from the first file which matches the key in the second file, followed by the lookup symbol. The symbols and types of lookups available are detailed in the following sections. For the example above, a many-to-one relationship would probably exist, that is many sales for each customer. The symbol for a many-to-one relationship is ->, so that would be used in the lookup specification.
Example: customer name->
* Section *
18.1.4 Which Key to Use
Here enter the key number of the key field. This needs to match the key number included in the file definition for the looked up file, and if you do not have key fields defined, then you need to go back to that file definition and enter the starting location of the key fields on the file definition. If you have only one key field, use 1.
Example: 1
* Section *
18.1.5 Type of Relationship
Enter the type of relationship which exists between the two files. Types of file relationships available are:
Keyed Read field 1 -> field 2Keyed Read based on Native Index field 1 ->> field 2
One-to-Many Keyed Read field 1 +> field 2
Based on Native Index field 1 +>> field 2
One-to-Many Keyed Read Chain field 1 +> field 2 +> field 3
Direct Read field 1 => field 2 or "n" => field 2
One-to-Many Direct Read Chain field 1 *> field 2 *> field 3
Enter the symbol for the type of relationship that exists. For the example above, use ->, the keyed read lookup.
Click OK to save the file relationship. You can go back to a design which uses the First File as the main file for your design, and you can select Special Commands and Field from Another File to see the file defined here in the list box.
Following are sections on each type of lookup. You may not need to read every page of this chapter in order to define relationships and combine files in report!
* Section *
18.2 Keyed Read Look-ups
A keyed read is done by matching the look-up field with the key field of the file being looked up. Here are some definitions:
The Lookup Field. This is a field in the primary data file used for the report which matches the key field of file being looked up.
It is used for when entering the look-up specification on the file relationship.
Key Field. This is the field of the looked up file which contains the key data. Its entries are unique (e.g. account number). The contents of the Lookup Field will match the contents of the Key Field.
The Key Field is not entered on the look-up specification on the file relationship menu, but it is used internally by the program to locate looked-up field.
Looked up Field. This is the field that is "looked up" by the program and included in the report. This field is the one selected when designing the report. It is not entered on the File Relationships menu (unless it is also part of the specification).
* Section *
18.2.1 The Keyed Read Lookup Specification
This is the format for a keyed read look-up specification:
(File 1) (File 2)
LOOKUP FIELD LOOKED UP FIELD .key[n]
(Matches key field -> (Field to include (Number of keyof looked up file) in Report being used)
The field which matches the key field is on the left side of the
